﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Web.Security;
using MySql.Data.MySqlClient;
using log4net;
using System.Web;

namespace Possan.MySQL.Providers
{
	public class Role : RoleProvider
	{
		private string _connName;
		private string _connStr;
		private string _name;
		private ILog _log;

		private UserCache _usercache;
		private RoleCache _rolecache;

		public Role()
			: base()
		{
			_log = LogManager.GetLogger(typeof(Role));
			_name = "";
		}

		public override void Initialize(string name, System.Collections.Specialized.NameValueCollection config)
		{
			_log.Debug("Initialize( name=" + name + " )");

			base.Initialize(name, config);

			_connName = "";
			if (config["connectionStringName"] != null)
				_connName = config["connectionStringName"];

			_name = "";

			_connStr = "";
			System.Configuration.Configuration config2 = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration(HttpContext.Current.Request.ApplicationPath);
			_connStr = config2.ConnectionStrings.ConnectionStrings[_connName].ConnectionString;

			_usercache = new UserCache(_connStr);
			_rolecache = new RoleCache( _connStr );

			// config
		}

		MySqlConnection Connect()
		{
			// _log.Debug("Connect( _connName=" + _connName + " )");
			// this.sett
			MySqlConnection conn = new MySqlConnection(_connStr);
			return conn;
		}

		public override void AddUsersToRoles(string[] usernames, string[] roleNames)
		{
			_log.Debug("AddUsersToRoles( usernames=" + string.Join(",", usernames) + ", passwords=" + string.Join(",", roleNames) + " )");


			using (var conn = Connect())
			{
				conn.Open();

				var userids = new List<string>();
				foreach (string username in usernames)
				{ 
					string uid = _usercache.GetIdByUsername(username);
					if (!string.IsNullOrEmpty(uid)) 
						userids.Add(uid);
				}

				foreach (string rolename in roleNames)
				{
					string roleid =_rolecache.GetIdByName(rolename);

					using (MySqlCommand cmd = conn.CreateCommand())
					{
						cmd.CommandText = @"SELECT r.RoleId from aspnet_Roles r where r.LoweredRoleName = @a";
						cmd.Parameters.Add(new MySqlParameter("@a", rolename.ToLower()));
						using (MySqlDataReader rdr = cmd.ExecuteReader())
						{
							if (rdr.Read())
								roleid = rdr["RoleId"].ToString();

							rdr.Close();
						}
					}

					foreach (string userid in userids)
					{
						// adda om vi inte redan är med i rollen
						bool in_role_already = false;
						using (MySqlCommand cmd = conn.CreateCommand())
						{
							cmd.CommandText = @"SELECT count(*) as n FROM aspnet_UsersInRoles a where a.UserId = @a and a.RoleId = @b";
							cmd.Parameters.Add(new MySqlParameter("@a", userid));
							cmd.Parameters.Add(new MySqlParameter("@b", roleid));
							using (MySqlDataReader rdr = cmd.ExecuteReader())
							{
								if (rdr.Read())
								{
									int i = 0;
									int.TryParse(rdr["n"].ToString(), out i);
									in_role_already = (i > 0);
								}
								rdr.Close();
							}
						}

						if (!in_role_already)
						{
							using (MySqlCommand cmd = conn.CreateCommand())
							{
								cmd.CommandText = @"insert into aspnet_UsersInRoles ( userid, roleid ) values ( @a, @b )";
								cmd.Parameters.Add(new MySqlParameter("@a", userid));
								cmd.Parameters.Add(new MySqlParameter("@b", roleid));
								cmd.ExecuteNonQuery();
							}
						}
					}
				}

				conn.Close();
			}
		}

		public override string ApplicationName
		{
			get
			{
				_log.Debug("get ApplicationName (" + _name + ")");
				return _name;
			}
			set
			{
				_log.Debug("set ApplicationName=" + value);
				_name = value;
			}
		}

		public override void CreateRole(string roleName)
		{
			_log.Debug("CreateRole( roleName=" + roleName + " )");
			throw new NotImplementedException();
		}

		public override bool DeleteRole(string roleName, bool throwOnPopulatedRole)
		{
			_log.Debug("DeleteRole( roleName=" + roleName + ", throwOnPopulatedRole=" + throwOnPopulatedRole + " )");
			throw new NotImplementedException();
		}

		public override string[] FindUsersInRole(string roleName, string usernameToMatch)
		{
			_log.Debug("FindUsersInRole( roleName=" + roleName + ", usernameToMatch=" + usernameToMatch + " )");
			throw new NotImplementedException();
		}

		public override string[] GetAllRoles()
		{
			_log.Debug("GetAllRoles()");

			List<string> roles = new List<string>();

			using (MySqlConnection conn = Connect())
			{
				conn.Open();
				using (MySqlCommand cmd = conn.CreateCommand())
				{
					cmd.CommandText = @"select LoweredRoleName from aspnet_Roles order by LoweredRoleName asc";
					// cmd.Parameters.Add(new MySqlParameter("@a", username));
					using (MySqlDataReader rdr = cmd.ExecuteReader())
					{
						while (rdr.Read())
						{
							roles.Add(rdr["LoweredRoleName"].ToString().ToLower());
						}
						rdr.Close();
					}
					cmd.Dispose();
				}
				conn.Close();
			}

			return roles.ToArray();
		}

		public override string[] GetRolesForUser(string username)
		{
			_log.Debug("GetRolesForUser( username=" + username + " )");

			List<string> roles = new List<string>();

			using (MySqlConnection conn = Connect())
			{
				conn.Open();
				using (MySqlCommand cmd = conn.CreateCommand())
				{
					cmd.CommandText = @"SELECT r.LoweredRoleName FROM aspnet_roles r
						inner join aspnet_usersinroles a on a.RoleId = r.RoleId
						inner join  aspnet_users u on u.UserId = a.UserId
						and u.UserName = @a";
					cmd.Parameters.Add(new MySqlParameter("@a", username));
					using (MySqlDataReader rdr = cmd.ExecuteReader())
					{
						while (rdr.Read())
						{
							roles.Add(rdr["LoweredRoleName"].ToString());
						}
						rdr.Close();
					}
					cmd.Dispose();
				}
				conn.Close();
			}

			string[] arr = roles.ToArray();

			_log.Debug("GetRolesForUser returning: " + string.Join(",", arr));
			return arr;
		}

		public override string[] GetUsersInRole(string roleName)
		{
			_log.Debug("GetUsersInRole( roleName=" + roleName + " )");

			var users = new List<string>();
			using (MySqlConnection conn = Connect())
			{
				conn.Open();
				using (MySqlCommand cmd = conn.CreateCommand())
				{
					cmd.CommandText = @"SELECT u.UserName FROM aspnet_users u
						inner join aspnet_usersinroles a on a.UserId = u.UserId
						inner join aspnet_roles r on r.RoleId = a.RoleId
						and r.LoweredRoleName = @a";
					cmd.Parameters.Add(new MySqlParameter("@a", roleName.ToLower()));
					using (MySqlDataReader rdr = cmd.ExecuteReader())
					{
						while (rdr.Read())
						{
							users.Add(rdr["UserName"].ToString());
						}
						rdr.Close();
					}
					cmd.Dispose();
				}
				conn.Close();
			}

			string[] arr = users.ToArray();

			_log.Debug("GetUsersInRole returning: " + string.Join(",", arr));
			return arr;
		}

		public override bool IsUserInRole(string userName, string roleName)
		{
			_log.Debug("IsUserInRole( userName=" + userName + ", roleName=" + roleName + " )");

			bool ret = false;

			using (MySqlConnection conn = Connect())
			{
				conn.Open();
				using (MySqlCommand cmd = conn.CreateCommand())
				{
					cmd.CommandText = @"SELECT count(*) as n FROM aspnet_Users u
						inner join aspnet_UsersInRoles a on a.UserId = u.UserId
						inner join aspnet_Roles r on r.RoleId = a.RoleId
						where r.RoleName=@a and u.UserName=@b";
					cmd.Parameters.Add(new MySqlParameter("@a", roleName));
					cmd.Parameters.Add(new MySqlParameter("@b", userName));
					using (MySqlDataReader rdr = cmd.ExecuteReader())
					{
						if (rdr.Read())
						{
							int i = 0;
							int.TryParse(rdr["n"].ToString(), out i);
							ret = (i > 0);
						}
						rdr.Close();
					}
					cmd.Dispose();
				}
				conn.Close();
			}

			_log.Debug("IsUserInRole returning: " + ret);

			return ret;
		}

		public override void RemoveUsersFromRoles(string[] usernames, string[] roleNames)
		{
			_log.Debug("RemoveUsersFromRoles( usernames=" + string.Join(",", usernames) + ", passwords=" + string.Join(",", roleNames) + " )");
			// throw new NotImplementedException();


			using (MySqlConnection conn = Connect())
			{
				conn.Open();

				foreach (string username in usernames)
				{
					string userid = _usercache.GetIdByUsername(username);
					if (userid != "")
					{
						foreach (string role in roleNames)
						{
							string roleid = "";
							using (MySqlCommand cmd = conn.CreateCommand())
							{
								cmd.CommandText = @"SELECT r.RoleId FROM aspnet_Roles r on r.RoleName = @a";
								cmd.Parameters.Add(new MySqlParameter("@a", role));
								using (MySqlDataReader rdr = cmd.ExecuteReader())
								{
									if (rdr.Read())
									{
										roleid = rdr["RoleID"].ToString();
									}
									rdr.Close();
								}
								cmd.Dispose();
							}

							if (roleid != "")
							{
								using (MySqlCommand cmd = conn.CreateCommand())
								{
									cmd.CommandText = @"DELETE FROM aspnet_UsersInRoles WHERE RoleId=@a AND UserId=@b";
									cmd.Parameters.Add(new MySqlParameter("@a", roleid));
									cmd.Parameters.Add(new MySqlParameter("@b", userid));
									cmd.ExecuteNonQuery();
									cmd.Dispose();
								}
							}
						}
					}
				}

				conn.Close();
			}
		}

		public override bool RoleExists(string roleName)
		{
			_log.Debug("RoleExists( roleName=" + roleName + " )");

			bool ret = false;

			using (MySqlConnection conn = Connect())
			{
				conn.Open();
				using (MySqlCommand cmd = conn.CreateCommand())
				{
					cmd.CommandText = @"SELECT count(*) as n FROM aspnet_Roles r where r.LoweredRoleName=@a";
					cmd.Parameters.Add(new MySqlParameter("@a", roleName.ToLower()));
					using (MySqlDataReader rdr = cmd.ExecuteReader())
					{
						if (rdr.Read())
						{
							int i = 0;
							int.TryParse(rdr["n"].ToString(), out i);
							ret = (i > 0);
						}
						rdr.Close();
					}
					cmd.Dispose();
				}
				conn.Close();
			}

			return ret;
		}
	}
}
